package com.rcp.host;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.rcp.objects.EmployeeBean;
import com.rcp.objects.ReservationBean;

/**
 * Servlet implementation class HostServlet
 */
@WebServlet("/HostServlet")
public class HostServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	String className = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://cs3.calstatela.edu:3306/cs437group01";
	String dbusername = "cs437group01";
	String dbpassword = "VHj#dj!v";
	
    /**
     * @see HttpServlet#HttpServlet()
     */
    public HostServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		String pathToView = "/WEB-INF/host/WelcomeHost.jsp";
		
		HttpSession session = request.getSession();
		// If Employee did not use login page, but has previously logged in and clicked remember me
		// Search for any cookies with Employee login
		if(session.getAttribute("loggedIn") == null){

			// Get reference to cookies
			Cookie[] cookies = request.getCookies();
			// Check if cookies has any data
			if( cookies != null ){
				// Look for data correct data
				for( int i = 0; i < cookies.length; i++ ){
					// get a reference to the current cookie
					Cookie cookie = cookies[i];
					// If the cookie is the 'Employee' cookie
					// then create a session variables for that Employee
					if(cookie.getName().equals("EmployeeID")){

						// Create the Session variables
						session.setAttribute("loggedIn", true);					
						session.setAttribute("EmployeeID", cookie.getValue());
						
						ResultSet resultSet = null;
						
						try{
							
							// Include the driver class
							Class.forName( className );
							
							// Establish a connection to the database
							Connection connection =
								DriverManager.getConnection(url, dbusername, dbpassword);			
							
							// Get a reference to the statement object that will execute our query
							// on the server
							Statement statement = connection.createStatement();
							
							resultSet = statement.executeQuery("SELECT * FROM Employees WHERE EmployeeID = " + cookie.getValue().trim());

							EmployeeBean employee = null;
							
							while(resultSet.next()){
								
								employee = new EmployeeBean(resultSet.getInt("EmployeeID"), resultSet.getString("FirstName"), resultSet.getString("LastName"), resultSet.getInt("status"));
								
							}
							
							session.setAttribute("employee", employee);
							
							resultSet = null;
							
							// Close our DB Connection
							connection.close();
													
						}
						catch(SQLException e){		
							e.printStackTrace();
						}
						catch(ClassNotFoundException e){
							e.printStackTrace();
						}
						
						
						break;

					}
				}
			}

		}
		
		if (session.getAttribute("loggedIn") != null){
		
			ResultSet resultSet = null;
			
			try{
				
				// Include the driver class
				Class.forName( className );
				
				// Establish a connection to the database
				Connection connection =
					DriverManager.getConnection(url, dbusername, dbpassword);			
				
				// Get a reference to the statement object that will execute our query
				// on the server
				Statement statement = connection.createStatement();
			
				if(request.getParameter("removeres") != null){
					
					statement.executeUpdate("DELETE FROM Reservations WHERE ReservationID = " + request.getParameter("removeres").toString().trim());
					
				}
				
				// Get reservations for today
				ArrayList<ReservationBean> reservations = new ArrayList<ReservationBean>();
				
				resultSet = statement.executeQuery("SELECT R.ReservationID, R.CustomerID, R.ReservationTime, C.FirstName, C.LastName FROM Reservations R LEFT JOIN Customers C ON R.CustomerID = C.CustomerID  WHERE R.ReservationTime BETWEEN DATE_SUB(now(), INTERVAL 1 HOUR) AND DATE_ADD(now(), INTERVAL 24 HOUR) ORDER BY R.ReservationTime ASC");
	
				while(resultSet.next()){
					
					reservations.add(new ReservationBean( resultSet.getInt("ReservationID"), resultSet.getInt("CustomerID"), resultSet.getString("ReservationTime"), resultSet.getString("FirstName") + " " + resultSet.getString("LastName")));
					
				}
				
				request.setAttribute("reservations", reservations);
				
				resultSet = null;
				
				// Close our DB Connection
				connection.close();
										
			}
			catch(SQLException e){		
				e.printStackTrace();
			}
			catch(ClassNotFoundException e){
				e.printStackTrace();
			}
			
		} else {
			
			request.setAttribute("action", "message2");
			
			pathToView = "/WEB-INF/login/Login.jsp";
			
		}
		
		RequestDispatcher dispatcher =
				request.getRequestDispatcher( pathToView );
			
			dispatcher.forward(request, response);
		
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}
